package com.jiedeng.mapper.provider;

import org.springframework.util.StringUtils;

/**
 * 首页mapper映射sql类
 *
 * @author zhouyb
 */
public class ProjectCountSqlProvider {
    /**
     * 分页查询首页统计数据sql
     *
     * @param epsId
     * @param projectName
     * @param status
     * @param page
     * @param pageSize
     * @return
     */
    public String findProjectCountList(long epsId, String projectName, String status, String page, String pageSize, String corpIds) {
        int pagenum = Integer.parseInt(pageSize) * (Integer.parseInt(page) - 1);
        String sql = "select t.Project_ID ,t.Project_Caption,count(TaskID) checkNum,count(closed_loop) closedLoopNum," +
                " count(this_month) thisMonthNum,count(this_season) thisSeasonNum" +
                " from(" +
                " SELECT DISTINCT tp.Project_ID, tp.Project_Caption, tt.TaskID," +
                " CASE WHEN tt.IsDo = 1 THEN tt.TaskID ELSE NULL END closed_loop," +
                "  CASE WHEN date_format(tt.Start_Time, '%Y-%m') = date_format(NOW(), '%Y-%m') THEN tt.TaskID ELSE NULL END this_month," +
                "  CASE WHEN QUARTER (tt.Start_Time) = QUARTER (now()) THEN tt.TaskID ELSE NULL END this_season" +
                " FROM t_project tp LEFT JOIN t_task tt ON tt.Project_ID = tp.Project_ID " +
                " LEFT JOIN t_project_corp tpc on tpc.Project_ID=tp.Project_ID " +
                "WHERE 1=1 ";
        if (epsId != 0) {
            sql += " and  tp.EPS_ID = " + epsId;
        }
        if (!StringUtils.isEmpty(projectName)) {
            sql += " and tp.Project_Caption like '%" + projectName + "%' ";
        }
        if (!StringUtils.isEmpty(status)) {
            sql += " and tp.Status = '" + status + "' ";
        }
        if (!StringUtils.isEmpty(corpIds)) {
            sql += " and tpc.Corp_ID in (" + corpIds + ") ";
        }

        sql += " ) t " +
                " GROUP BY t.Project_ID,t.Project_Caption";
        sql += " order by t.Project_ID desc ";
        if (!StringUtils.isEmpty(pageSize)) {
            sql += "  LIMIT " + pagenum + "," + pageSize;
        }
        return sql;
    }

    /**
     * 查询统计总数sql
     *
     * @param epsId
     * @param projectName
     * @param status
     * @return
     */
    public String findAllProjectCount(long epsId, String projectName, String status, String corpIds) {
        String sql = "select IFNULL( count(*),0) countNum from (select " +
                " t.Project_ID  " +
                " from(" +
                " SELECT tp.Project_ID, tp.Project_Caption, tt.TaskID," +
                " CASE WHEN tt.IsCloseDo = 0 THEN tt.TaskID ELSE NULL END closed_loop," +
                "  CASE WHEN date_format(tt.Create_Time, '%Y-%m') = date_format(NOW(), '%Y-%m') THEN tt.TaskID ELSE NULL END this_month," +
                "  CASE WHEN QUARTER (tt.Create_Time) = QUARTER (now()) THEN tt.TaskID ELSE NULL END this_season" +
                " FROM t_project tp LEFT JOIN t_task tt ON tt.Project_ID = tp.Project_ID" +
                " LEFT JOIN t_project_corp tpc on tpc.Project_ID=tp.Project_ID " +
                " WHERE 1=1";
        if (epsId != 0) {
            sql += " and  tp.EPS_ID = " + epsId;
        }
        if (!StringUtils.isEmpty(projectName)) {
            sql += " and tp.Project_Caption like '%" + projectName + "%' ";
        }
        if (!StringUtils.isEmpty(status)) {
            sql += " and tp.Status = '" + status + "' ";
        }
        if (!StringUtils.isEmpty(corpIds)) {
            sql += " and tpc.Corp_ID in (" + corpIds + ") ";
        }
        sql += " ) t " +
                " GROUP BY t.Project_ID,t.Project_Caption ) tx";

        return sql;
    }

    /**
     * 分页查询照片下载
     *
     * @param taskId
     * @return
     */
    public String findPhotoCountList(long projectId, String taskId, int closeDo) {
        //int pagenum = Integer.parseInt(pageSize) * (Integer.parseInt(page) - 1);
        String sql = "select f.* from t_fileRecord f " +
                "INNER JOIN t_check ch on f.Check_ID = ch.ID " +
                "INNER join t_Task t on ch.Task_ID = t.TaskID " +
                "where f.CheckOrClose = " + closeDo;
        if (projectId != 0) {
            sql += " AND t.Project_ID = " + projectId;
        }
        if (!StringUtils.isEmpty(taskId)) {
            sql += " AND t.TaskID = '" + taskId + "' ";
        }
       /* if (!StringUtils.isEmpty(pageSize)) {
            sql += "  LIMIT " + pagenum + "," + pageSize;
        }*/
        return sql;
    }

    /**
     * 分页查询照片下载总数
     *
     * @param projectId
     * @return
     */
    public String findAllphotoCount(long projectId) {
        String sql = "select count(1) from t_fileRecord f " +
                "INNER JOIN t_check ch on f.Check_ID = ch.checkItem_ID " +
                "INNER join t_Task t on ch.Task_ID = t.TaskID " +
                "where f.type = 1 ";
        if (projectId != 0) {
            sql += " AND t.Project_ID = " + projectId;
        }

        return sql;
    }

    /**
     * 分页查询任务
     *
     * @param projectId
     * @param page
     * @param pageSize
     * @return
     */
    public String findtaskCountList(long projectId, String page, String pageSize) {
        int pagenum = Integer.parseInt(pageSize) * (Integer.parseInt(page) - 1);
        String sql = "select * from t_task ";
        if (projectId != 0) {
            sql += " where Project_ID = " + projectId;
        }
        sql += " order by start_time  desc ";

        if (!StringUtils.isEmpty(pageSize)) {
            sql += "  LIMIT " + pagenum + "," + pageSize;
        }

        return sql;
    }

    /**
     * 分页查询任务总数
     *
     * @param projectId
     * @return
     */
    public String findtaskCountNum(long projectId) {
        String sql = "select count(1) from t_task ";
        if (projectId != 0) {
            sql += " where Project_ID = " + projectId;
        }
        return sql;
    }

    /**
     * 查询单位下所有工程
     *
     * @param projectName
     * @param status
     * @param corpIds
     * @return
     */
    public String findProjectByCorpIdsSql(String projectName, String status, String corpIds) {
        String sql = " SELECT tp.* " +
                " FROM t_project tp " +
                " LEFT JOIN t_project_corp tpc on tpc.Project_ID=tp.Project_ID " +
                "WHERE 1=1 ";
        if (!StringUtils.isEmpty(projectName)) {
            sql += " and tp.Project_Caption like '%" + projectName + "%' ";
        }
        if (!StringUtils.isEmpty(status)) {
            sql += " and tp.Status = '" + status + "' ";
        }
        if (!StringUtils.isEmpty(corpIds)) {
            sql += " and tpc.Corp_ID in (" + corpIds + ") ";
        }

        sql += " order by tp.Project_ID desc ";

        return sql;
    }

    /**
     * 根据项目查询单位
     *
     * @param projectId
     * @return
     */
    public String findProjectCorpByProjectIdSql(long projectId) {
        String sql = "select tpc.id,tpc.Corp_ID corpId,tc.Corp_Caption corpName,tc.corpPropCaption ," +
                "tpc.Project_ID projectId, tpc.Corp_Prop_ID corpPropId " +
                " from t_project_corp tpc LEFT JOIN t_corp tc on tpc.Corp_ID=tc.Corp_ID  where  tpc.Project_ID=" + projectId;
        return sql;
    }

    /**
     * 根据单位和项目删除项目人员
     *
     * @param projectId
     * @param corpId
     */
    public String deleteProjectPersonByCorpIdAndProjectIdSql(long corpId, long projectId) {
        String sql = "delete from t_project_person " +
                "where project_id = " + projectId +
                " and person_id in (select tp.Person_ID  from t_person tp  where tp.Corp_ID = " + corpId + ")";
        return sql;
    }

    /**
     * 根据id查询项目详情
     *
     * @param projectId
     * @return
     */
    public String findProjectDetailsByIdSql(long projectId) {
        String sql = "SELECT " +
                " tp.Project_ID id, " +
                " tp.Project_Caption caption, " +
                " tp.Project_Cap cap, " +
                " tp.Project_code projectCode, " +
                " te.eps_Caption epsName, " +
                " tz.Zone_Caption zoneName, " +
                " tp.Memo, " +
                " tp.stime, " +
                " tp.etime, " +
                "tpt.ProType_Caption proTypeName, " +
                "tpd.ProDegree_Caption proDegreeName, " +
                "tper.name createrName, " +
                "tp.addtime, " +
                "cheper. NAME checkerName, " +
                "tp.contactinfo " +
                "FROM " +
                " t_project tp " +
                "LEFT JOIN t_eps te ON te.eps_ID = tp.EPS_ID " +
                "LEFT JOIN t_zone tz ON tz.zone_id = tp.zone_id " +
                "LEFT JOIN t_protype tpt on tpt.ProType_ID = tp.ProType_ID " +
                "LEFT JOIN t_prodegree tpd on tpd.ProDegree_ID = tp.ProDegree_ID " +
                "LEFT JOIN t_person tper on tper.Person_ID = tp.creater " +
                "LEFT JOIN t_person cheper ON cheper.Person_ID = tp.checker " +
                " " +
                "where tp.Project_ID = " + projectId;
        return sql;
    }

    /**
     * 根据单位和用户查询项目
     *
     * @param corpIds
     * @param personId
     * @return
     */
    public String findProjectByCorpIdsAndPersonIdSql(String corpIds, long personId) {
        String sql = "SELECT " +
                " tp.Project_ID id, " +
                " tp.Project_Caption caption, " +
                " tp.Project_Cap cap " +
                " FROM t_project tp  " +
                "                LEFT JOIN t_project_corp tpc on tpc.Project_ID=tp.Project_ID  " +
                "                WHERE 1=1 and tpc.Corp_ID in (" + corpIds + ") " +
                "UNION " +
                "SELECT " +
                " tp.Project_ID id, " +
                " tp.Project_Caption caption, " +
                " tp.Project_Cap cap " +
                " FROM t_project tp " +
                "                LEFT JOIN t_project_person tpp on tpp.Project_ID=tp.Project_ID  " +
                "                WHERE 1=1 and tpp.Person_ID = " + personId;
        return sql;
    }

    public String findCorpPropAndCorpByProjectIdSql(long personId) {
        String sql = " SELECT " +
                " tcp.Corp_Porp_Caption,tc.Corp_Caption " +
                " FROM " +
                " t_project_corp tpc " +
                " LEFT JOIN t_corp_prop tcp ON tcp.Corp_Prop_ID = tpc.Corp_Prop_ID " +
                " LEFT JOIN t_corp tc ON tc.Corp_ID = tpc.Corp_ID " +
                " WHERE tpc.Project_ID = " + personId;
        return sql;
    }
}
